package com.zlyx.easy.pio.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zlyx.easy.core.collections.Lists;
import com.zlyx.easy.core.utils.StringUtils;

/**
 * @Auth 赵光
 * @Describle
 * @2020年3月15日
 */
public class PioUtil {

	/**
	 * 读取excel(存在则读取，没有则新建)
	 * 
	 * @param filePath 读取路径
	 * @param sheetAt  表格坐标
	 * @return
	 * @throws Exception
	 */
	public static Workbook getExcel(String filePath) throws Exception {
		Workbook workbook = new XSSFWorkbook();
		File file = new File(filePath);
		if (file.exists()) {
			InputStream fis = new FileInputStream(filePath);
			if (filePath.endsWith(".xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (filePath.endsWith(".xls") || filePath.endsWith(".et")) {
				workbook = new HSSFWorkbook(fis);
			}
			fis.close();
		}
		return workbook;
	}

	/**
	 * 插入行数据
	 * 
	 * @param workbook 工作簿
	 * @param sheetAt  表格坐标
	 * @param title    表格标题
	 * @param values   值
	 * @param rowNum   插入行号
	 * @param offset   偏移量
	 * @return
	 * @throws Exception
	 */
	public static Workbook insertRow(Workbook workbook, int sheetAt, String title, List<Object> values, int rowNum,
			int offset) throws Exception {
		Sheet sheet = getSheet(workbook, sheetAt, title);
		Row row = sheet.getRow(rowNum);
		if (row != null) {
			sheet.removeRow(row);
		}
		row = sheet.createRow(rowNum);
		for (int i = 0; i < values.size(); i++) {
			Cell cell = row.getCell(i + offset);
			if (cell == null) {
				cell = row.createCell(i + offset);
			}
			cell.setCellValue(String.valueOf(values.get(i)));
		}
		return workbook;
	}

	/**
	 * 插入列数据
	 * 
	 * @param workbook  工作簿
	 * @param sheetAt   表格坐标
	 * @param title     表格标题
	 * @param values    值
	 * @param columnNum 插入列号
	 * @param offset    偏移量
	 * @return
	 * @throws Exception
	 */
	public static Workbook insertColumn(Workbook workbook, int sheetAt, String title, List<Object> values,
			int columnNum, int offset) throws Exception {
		Sheet sheet = getSheet(workbook, sheetAt, title);
		for (int i = 0; i < values.size(); i++) {
			Row row = sheet.getRow(i + offset);
			if (row == null) {
				row = sheet.createRow(i + offset);
			}
			Cell cell = row.getCell(columnNum);
			if (cell == null) {
				cell = row.createCell(columnNum);
			}
			cell.setCellValue(String.valueOf(values.get(i)));
		}
		return workbook;
	}

	/**
	 * 解析sheet
	 * 
	 * @param workbook 工作簿
	 * @param sheetAt  坐标
	 * @param title    标题
	 * @return
	 */
	public static Sheet getSheet(Workbook workbook, int sheetAt, String title) {
		int size = sheetAt - workbook.getNumberOfSheets();
		if (size >= 0) {
			for (int i = 0; i <= size; i++) {
				if (i == size && StringUtils.isNotEmpty(title)) {
					workbook.createSheet(title);
				} else {
					workbook.createSheet();
				}
			}
		}
		return workbook.getSheetAt(sheetAt);
	}

	/**
	 * 写出excel
	 * 
	 * @param workbook 工作簿
	 * @param filePath 写出路径
	 * @throws Exception
	 */
	public static void writeExcel(Workbook workbook, String filePath) throws Exception {
		File file = new File(filePath);
		File parrnetFile = file.getParentFile();
		if (!parrnetFile.exists()) {
			parrnetFile.mkdirs();
		}
		FileOutputStream os = new FileOutputStream(filePath);
		workbook.write(os);
		os.flush();
		os.close();
		System.out.println("写出excel完成！");
	}

	/**
	 * 解析表格表头
	 * 
	 * @param sheet
	 * @return
	 */
	public static List<String> getHeaders(Sheet sheet) {
		List<String> headers = Lists.newList();
		Row headerRow = sheet.getRow(0);
		if (headerRow != null) {
			headerRow.forEach(header -> {
				headers.add(header.getStringCellValue());
			});
		}
		return headers;
	}

}
